{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b9261e7f-2a1f-4df0-98f7-1b350c849736",
   "metadata": {},
   "source": [
    "## A01_Explore the data and choose the KPI"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02483f8b-9aa3-4986-98ae-dd7e0ba46907",
   "metadata": {},
   "source": [
    "### 1. init spark session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c6320c4b-f038-469d-992d-76901a5737a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4ac6681b-77f5-4fd3-98a5-c689e1ca76d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.appName(\"price_opendata\").getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e62b348a-b4d6-4e71-b6c2-cdbb022c9c71",
   "metadata": {},
   "source": [
    "### 2. read the dataset file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ee962a9b-3a28-484d-9086-8a9fd98242bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_dir = \"./Landing Zone/price_opendata\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "468b2ba9-5ea0-4097-bcb3-62b6e3694554",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.json(data_dir)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b7f68e6-8b04-493c-94ff-cb69cb791808",
   "metadata": {},
   "source": [
    "### 3. show dataframe datas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "284d3017-e4a4-4ea5-9408-6549d3c10de1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-----------+--------------+--------------------+--------------------+\n",
      "|_id|district_id| district_name|                info|         neigh_name |\n",
      "+---+-----------+--------------+--------------------+--------------------+\n",
      "|  1|          1|  Ciutat Vella|[{97.0, 1726.5, N...|            el Raval|\n",
      "|  2|          1|  Ciutat Vella|[{212.4, 2448.2, ...|      el Barri Gòtic|\n",
      "|  3|          1|  Ciutat Vella|[{175.5, 3476.3, ...|      la Barceloneta|\n",
      "|  4|          1|  Ciutat Vella|[{190.4, 2846.5, ...|Sant Pere, Santa ...|\n",
      "|  5|          2|      Eixample|[{268.3, 2842.9, ...|       el Fort Pienc|\n",
      "|  6|          2|      Eixample|[{192.4, 2938.7, ...|  la Sagrada Família|\n",
      "|  7|          2|      Eixample|[{306.9, 2798.2, ...|la Dreta de l'Eix...|\n",
      "|  8|          2|      Eixample|[{239.2, 2760.2, ...|l'Antiga Esquerra...|\n",
      "|  9|          2|      Eixample|[{301.6, 3648.2, ...|la Nova Esquerra ...|\n",
      "| 10|          2|      Eixample|[{177.5, 2024.9, ...|         Sant Antoni|\n",
      "| 11|          3|Sants-Montjuïc|[{93.7, 1582.1, N...|        el Poble Sec|\n",
      "| 12|          3|Sants-Montjuïc|[{166.2, 2405.2, ...|   la Marina de Port|\n",
      "| 13|          3|Sants-Montjuïc|[{179.3, 2543.5, ...|la Font de la Gua...|\n",
      "| 14|          3|Sants-Montjuïc|[{177.8, 1834.2, ...|         Hostafrancs|\n",
      "| 15|          3|Sants-Montjuïc|[{102.4, 1960.8, ...|          la Bordeta|\n",
      "| 16|          3|Sants-Montjuïc|[{190.8, 3186.4, ...|       Sants - Badal|\n",
      "| 17|          3|Sants-Montjuïc|[{154.5, 2270.1, ...|               Sants|\n",
      "| 18|          4|     Les Corts|[{252.5, 2923.6, ...|           les Corts|\n",
      "| 19|          4|     Les Corts|[{266.7, 2890.5, ...|la Maternitat i S...|\n",
      "| 20|          4|     Les Corts|[{733.9, 4406.9, ...|           Pedralbes|\n",
      "+---+-----------+--------------+--------------------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a953a796-4a23-4c49-9152-574601003f5f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97270dbf-ad8e-4b39-9e1a-50b9787949f6",
   "metadata": {},
   "source": [
    "#### show the dataframe schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "613569dd-72c5-42d8-8f1d-9b69202124c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- _id: long (nullable = true)\n",
      " |-- district_id: long (nullable = true)\n",
      " |-- district_name: string (nullable = true)\n",
      " |-- info: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- Amount: double (nullable = true)\n",
      " |    |    |-- PerMeter: double (nullable = true)\n",
      " |    |    |-- diffAmount: double (nullable = true)\n",
      " |    |    |-- diffPerMeter: double (nullable = true)\n",
      " |    |    |-- usedAmount: double (nullable = true)\n",
      " |    |    |-- usedPerMeter: double (nullable = true)\n",
      " |    |    |-- year: long (nullable = true)\n",
      " |-- neigh_name : string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8918c5e9-31fd-44b7-a719-6877a3331806",
   "metadata": {},
   "source": [
    "### 4. show info datas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "d337df19-d77d-4ccd-9443-084a52ffe006",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import explode"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "0fcff3aa-1eff-499b-a6cd-a6e88ee7e029",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-----------+-------------+--------------------+------+--------+----------+------------+----------+------------+----+\n",
      "|_id|district_id|district_name|         neigh_name |Amount|PerMeter|diffAmount|diffPerMeter|usedAmount|usedPerMeter|year|\n",
      "+---+-----------+-------------+--------------------+------+--------+----------+------------+----------+------------+----+\n",
      "|  1|          1| Ciutat Vella|            el Raval|  97.0|  1726.5|      NULL|        NULL|      97.0|      1726.5|2013|\n",
      "|  1|          1| Ciutat Vella|            el Raval| 141.7|  2087.6|      99.1|      2534.3|     143.1|      2073.5|2014|\n",
      "|  1|          1| Ciutat Vella|            el Raval| 193.8|  2401.9|      NULL|        NULL|     193.8|      2401.9|2015|\n",
      "|  1|          1| Ciutat Vella|            el Raval| 181.0|  2805.2|      NULL|        NULL|     180.7|      2798.6|2016|\n",
      "|  1|          1| Ciutat Vella|            el Raval| 240.3|  3469.9|     292.5|      3633.1|     240.0|      3468.9|2017|\n",
      "|  2|          1| Ciutat Vella|      el Barri Gòtic| 212.4|  2448.2|     285.2|      3248.7|     193.4|      2239.7|2013|\n",
      "|  2|          1| Ciutat Vella|      el Barri Gòtic| 277.7|  2784.9|     240.3|      3063.2|     285.0|      2730.3|2014|\n",
      "|  2|          1| Ciutat Vella|      el Barri Gòtic| 386.3|  3193.4|     355.7|      2331.6|     393.8|      3404.2|2015|\n",
      "|  2|          1| Ciutat Vella|      el Barri Gòtic| 422.8|  4149.4|     409.2|      4700.7|     430.0|      3862.5|2016|\n",
      "|  2|          1| Ciutat Vella|      el Barri Gòtic| 472.7|  4565.8|     799.5|      5397.4|     424.0|      4441.9|2017|\n",
      "|  3|          1| Ciutat Vella|      la Barceloneta| 175.5|  3476.3|     228.7|      4062.8|     119.7|      2861.3|2013|\n",
      "|  3|          1| Ciutat Vella|      la Barceloneta| 166.0|  3744.9|     216.9|      4292.5|     142.6|      3493.3|2014|\n",
      "|  3|          1| Ciutat Vella|      la Barceloneta| 150.3|  3474.4|     148.0|      3587.0|     150.8|      3449.7|2015|\n",
      "|  3|          1| Ciutat Vella|      la Barceloneta| 212.2|  4151.9|     409.5|      4477.2|     186.4|      4109.5|2016|\n",
      "|  3|          1| Ciutat Vella|      la Barceloneta| 197.7|  4501.1|     192.8|      3967.5|     198.2|      4551.3|2017|\n",
      "|  4|          1| Ciutat Vella|Sant Pere, Santa ...| 190.4|  2846.5|     224.5|      3404.9|     180.9|      2691.4|2013|\n",
      "|  4|          1| Ciutat Vella|Sant Pere, Santa ...| 193.2|  2666.6|     198.9|      3177.1|     192.5|      2601.9|2014|\n",
      "|  4|          1| Ciutat Vella|Sant Pere, Santa ...| 248.7|  3479.4|     349.6|      5059.0|     239.6|      3335.8|2015|\n",
      "|  4|          1| Ciutat Vella|Sant Pere, Santa ...| 293.1|  3646.5|     473.7|      4753.5|     284.0|      3590.4|2016|\n",
      "|  4|          1| Ciutat Vella|Sant Pere, Santa ...| 314.3|  4593.7|     375.8|      5973.2|     310.4|      4506.9|2017|\n",
      "+---+-----------+-------------+--------------------+------+--------+----------+------------+----------+------------+----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"_id\", \"district_id\", \"district_name\", \"neigh_name \", explode(\"info\").alias(\"info\")) \\\n",
    "    .select(\"_id\", \"district_id\", \"district_name\", \"neigh_name \", \"info.*\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a61b7f05-7ab4-4ac9-a3d3-ba3957cff84f",
   "metadata": {},
   "source": [
    "### 5. dataset info conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d76c4186-057a-44a6-879a-5974efa12c39",
   "metadata": {},
   "source": [
    "This JSON data contains detailed records of real estate information for various neighborhoods in Barcelona. \n",
    "\n",
    "Each record corresponds to a neighborhood and includes the neighborhood name, the district it belongs to, and real estate transaction data over several years."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "958d2eb8-e99d-422a-8022-2d67344165e1",
   "metadata": {},
   "source": [
    "### 6. Choose the KPI"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c54dac2-067d-4062-b931-21e79aecf088",
   "metadata": {},
   "source": [
    "Based on this dataset, the following analysis objectives could be considered:\n",
    "\n",
    "- Trend Analysis Over Time：\n",
    "  - Price Trends：Analyze the trend of average prices per square meter (PerMeter) over the years for each neighborhood.\n",
    "- Correlation Analysis:\n",
    "  - Price and Transaction Volume：Investigate the correlation between average prices per square meter and total transaction amounts.\n",
    "- Distribution Analysis：\n",
    "  - Price Distribution: Analyze the distribution of average prices per square meter across neighborhoods.\n",
    "  - Transaction Amount Distribution: Examine the distribution of total transaction amounts across neighborhoods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6a5f51d-570b-4cfa-ba88-85c6db6ec6d4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
